--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

/*DROP TABLE se_pcp_repent;
CREATE TABLE se_pcp_repent AS (SELECT e.* FROM &erp.pcp_repent e WHERE 1=2);
DROP TABLE se_pcp_repdet;
CREATE TABLE se_pcp_repdet AS (SELECT d.* FROM &erp.pcp_repdet d WHERE 1=2);  */

DELETE FROM se_pcp_repent;
DELETE FROM se_pcp_repdet;





/*SELECT e.rep_cod,e.PERIODE_VAL,e.JOUR_DAT,e.COMBSTO_ID,d.*
FROM   &erp.pcp_repent e, &erp.pcp_repdet d
WHERE e.rep_cod='AN_ART_DIM'
  AND d.parentenreg_id(+)=e.enreg_id */

/* table de travail*/
DROP TABLE temp_repartitions_lpa_tpa;

CREATE TABLE temp_repartitions_lpa_tpa AS (
    SELECT p.cleprod,p.prodbase,p.lpa/*,mt.rangmulti2 rang_tpa*/,mt.tpa,/*pd.RANGMULTI2 rang_dat,*/pd.DATAP,po.pourc
    FROM uv_prepartitionpdp p
        ,uv_pmensurations m
        ,uv_pmensurations_tpa mt
        ,uv_prepartitionpdp_datap pd
        ,uv_prepartitionpdp_pourc po
    WHERE m.prodbase=p.prodbase AND m.LPA = p.lpa
      AND mt.id=m.id
      AND pd.id=p.id
      AND po.id=p.id
      AND po.RANGMULTI3=pd.RANGMULTI2 /*rang_dat*/
      AND po.RANGMULTI2=mt.RANGMULTI2 /*rang_tpa*/ );


UPDATE temp_repartitions_lpa_tpa SET lpa = REPLACE(lpa,' ','¤') WHERE lpa LIKE '% %' ;
UPDATE temp_repartitions_lpa_tpa SET lpa = REPLACE(lpa,'.',' ') WHERE lpa LIKE '%.%' ;
UPDATE temp_repartitions_lpa_tpa v SET lpa = trim(lpa)
 WHERE lpa LIKE '% %' AND Length(trim(lpa))>0;
UPDATE temp_repartitions_lpa_tpa SET lpa = REPLACE(lpa,' ','.') WHERE lpa LIKE '% %' ;
UPDATE temp_repartitions_lpa_tpa SET lpa = REPLACE(lpa,'¤',' ') WHERE lpa LIKE '%¤%' ;
UPDATE temp_repartitions_lpa_tpa v SET lpa = trim(lpa)
 WHERE lpa LIKE '% %' AND Length(trim(lpa))>0;

UPDATE  temp_repartitions_lpa_tpa SET lpa = 'STD' WHERE lpa ='....' ;

UPDATE temp_repartitions_lpa_tpa SET tpa = REPLACE(tpa,' ','¤') WHERE tpa LIKE '% %' ;
UPDATE temp_repartitions_lpa_tpa SET tpa = REPLACE(tpa,'.',' ') WHERE tpa LIKE '%.%' ;
UPDATE temp_repartitions_lpa_tpa v SET tpa = trim(tpa)
 WHERE tpa LIKE '% %' AND Length(trim(tpa))>0;
UPDATE temp_repartitions_lpa_tpa SET tpa = REPLACE(tpa,' ','.') WHERE tpa LIKE '% %' ;
UPDATE temp_repartitions_lpa_tpa SET tpa = REPLACE(tpa,'¤',' ') WHERE tpa LIKE '%¤%' ;
UPDATE temp_repartitions_lpa_tpa v SET tpa = trim(tpa)
 WHERE tpa LIKE '% %' AND Length(trim(tpa))>0;


PROMPT CREATE INDEX temp_repartitions_lpa_tpa_01
CREATE INDEX temp_repartitions_lpa_tpa_01
  ON temp_repartitions_lpa_tpa (
    CLEPROD
  )
  NOLOGGING
 ;

/*controle des lpa tpa non existantes dans le tableau de mensuration*/
/*SELECT * FROM temp_repartitions_lpa_tpa r
WHERE NOT EXISTS (SELECT 1 FROM &erp.met_mensurlt m WHERE r.prodbase=m.gene_cod || gene_ind AND m.LONGPAT_COD=r.lpa AND m.TAILLEPAT_COD=r.tpa) ; */

 INSERT INTO se_pcp_repent
 (SELECT distinct
      'ART_DIM' rep_cod,
      Trunc(To_Date('31/12/1967','dd/mm/yyyy')+c.datap,'YEAR') deb_dat,
      NULL fin_dat,
      NULL /*To_Char(To_Date('31/12/1967','dd/mm/yyyy')+c.datap,'yyyy')*/ periode_val,
      NULL /*Trunc(To_Date('31/12/1967','dd/mm/yyyy')+c.datap,'YEAR')*/ jour_dat,
      Decode(p.codgestion,'5',conv.combsto_ref,p.numspecif) combsto_id,
      NULL unfab_cod,
      NULL plan_cod,
      NULL cpte_cod,
      0 enreg_id,
      NULL libre_val
  FROM temp_repartitions_lpa_tpa c, uv_pproduits p, temp_conv_rgp_combstoref conv
  WHERE c.CLEPROD=p.id(+)
    AND conv.clergp(+)=p.id ) ;

 DELETE FROM se_pcp_repent WHERE rep_cod='ART_DIM' AND COMBSTO_ID IS NULL ;
 DELETE FROM se_pcp_repent r WHERE NOT EXISTS (SELECT 1 FROM &erp.art_comp c WHERE c.combsto_id= r.combsto_id) ;

 UPDATE se_pcp_repent tf SET enreg_id=Nvl((select Max(enreg_id) FROM &erp.pcp_repent f WHERE f.rep_cod=tf.rep_cod AND f.combsto_id =tf.combsto_id AND f.deb_dat=tf.deb_dat),0) ;
 UPDATE se_pcp_repent tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id=0 ;




 INSERT INTO se_pcp_repdet
 (SELECT --distinct
      --Decode(p.codgestion,'5',conv.combsto_ref,p.numspecif)+0 parentenreg_id,
      Nvl((SELECT ( enreg_id) FROM se_pcp_repent e
       WHERE e.rep_cod='ART_DIM'
         AND e.combsto_id=Decode(p.codgestion,'5',conv.combsto_ref,p.numspecif)
         AND e.deb_dat=Trunc(To_Date('31/12/1967','dd/mm/yyyy')+c.datap,'YEAR')
         ),0)   parentenreg_id,
      NULL periode_val,
      NULL jour_dat,
      NULL combsto_id,
      null unfab_cod,
      NULL cpte_cod,
      NULL plan_cod,
      0 enreg_id,
      NULL repp_val,
      Nvl(c.pourc,0) repc_val,
      lpa || 'x' || tpa libre_val
  FROM temp_repartitions_lpa_tpa c, uv_pproduits p, (SELECT DISTINCT clergp,COMBSTO_REF FROM temp_conv_rgp_combstoref) conv
  WHERE c.CLEPROD=p.id(+)
    --AND c.cleprod LIKE '13280261155%'
    AND conv.clergp(+)=p.id ) ;


 DELETE FROM se_pcp_repdet WHERE parentenreg_id=0 ;
 --DELETE FROM &erp.pcp_repdet d WHERE EXISTS (SELECT 1 FROM se_pcp_repdet dd WHERE dd.parentenreg_id=d.parentenreg_id)  ;


/* DELETE FROM se_pcp_repdet d WHERE EXISTS
    (SELECT 1 FROM (
      SELECT d.PARENTENREG_ID,d.LIBRE_VAL,outils_mli2.stragg(d.enreg_id)
      FROM  se_pcp_repdet d
      WHERE EXISTS (SELECT 1 FROM se_pcp_repent e WHERE e.enreg_id=d.parentenreg_id AND rep_cod ='AN_ART_DIM')
      GROUP BY d.PARENTENREG_ID,d.LIBRE_VAL HAVING Count(*)>1  ) r
    WHERE r.PARENTENREG_ID=d.PARENTENREG_ID )
      AND d.repc_val=0 ;    */



 UPDATE se_pcp_repdet tf SET enreg_id=Nvl((select enreg_id FROM &erp.pcp_repdet f WHERE f.parentenreg_id=tf.parentenreg_id AND f.libre_val=tf.libre_val),0) ;
 UPDATE se_pcp_repdet tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id=0;


 DELETE &erp.pcp_repdet d WHERE EXISTS (SELECT 1 FROM &erp.pcp_repent e WHERE d.parentenreg_id=e.enreg_id AND rep_cod ='ART_DIM') ;
 DELETE &erp.pcp_repent d WHERE EXISTS (SELECT 1 FROM &erp.pcp_repent e WHERE d.enreg_id=e.enreg_id AND rep_cod ='ART_DIM') ;

 DELETE &erp.pcp_repdet d WHERE EXISTS (SELECT 1 FROM &erp.pcp_repent e WHERE d.parentenreg_id=e.enreg_id AND rep_cod ='ART_CBS') ;
 DELETE &erp.pcp_repent d WHERE EXISTS (SELECT 1 FROM &erp.pcp_repent e WHERE d.enreg_id=e.enreg_id AND rep_cod ='ART_CBS') ;



 CALL uvse_synchroverserp('rep_cod@deb_dat@combsto_id','PCP_REPENT','O') ;
 CALL uvse_synchroverserp('parentenreg_id@libre_val','PCP_REPDET','O') ;


 commit;


select * from (
SELECT &outils.stragg(source) source,rep_cod, deb_dat, fin_dat, periode_val, jour_dat, combsto_id, unfab_cod, plan_cod, cpte_cod, enreg_id, libre_val
FROM (
  SELECT 'uv' source,e.* FROM   se_pcp_repent e
  WHERE e.rep_cod='ART_DIM'

  UNION ALL
  SELECT 'se' source,e.* FROM   &erp.pcp_repent e
  WHERE e.rep_cod='ART_DIM'
  )
GROUP BY rep_cod, deb_dat, fin_dat, periode_val, jour_dat, combsto_id, unfab_cod, plan_cod, cpte_cod, enreg_id, libre_val
HAVING Count(1)!=2) r
ORDER BY combsto_id ;